package com.fau.sd.servlet;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

import com.fau.sd.util.UuidUtil;

/**
 * @desc 同步软交换中继、队列、外线号码servlet 
 * @author kangyj
 * @datetime 2015年1月13日,下午6:17:17
 */
@WebServlet("/SyncServlet")
public class SyncServlet extends HttpServlet {
       
	private static final long serialVersionUID = 9087633941945956907L;

    public SyncServlet() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		String type = request.getParameter("type");
		ServletContext servletContext = this.getServletContext();  
        WebApplicationContext context = WebApplicationContextUtils.getWebApplicationContext(servletContext);
        
        JdbcTemplate jdbcSrc = (JdbcTemplate) context.getBean("jdbcSrc");
        JdbcTemplate jdbcTarget = (JdbcTemplate) context.getBean("jdbcTarget");
        
        switch (type) {
			case "1":				
				this.saveTrunk(jdbcSrc, jdbcTarget);		// 中继算话费(业务表中存的中继id, 查不到中继时，中继列就空，所以可以全部删除重新添加)
				break;
			case "2":
				this.saveOutSide(jdbcSrc, jdbcTarget);		// 外线号码
				this.updateOutSideNum(jdbcSrc, jdbcTarget);
				break;
			case "3":
				this.saveQueue(jdbcSrc, jdbcTarget);		// 同步cti队列到业务系统
				this.updateQueue(jdbcSrc, jdbcTarget);		// 更新业务系统队列号
				break;
			case "4":
				this.saveExtension(jdbcSrc, jdbcTarget);	// 同步cti分机到业务系统
				this.updateExtension(jdbcSrc, jdbcTarget);	// 更新业务系统分机号
				break;
		}
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

	/**
	 * @desc 同步中继表到业务系统
	 * @author kangyj
	 * @datetime 2015年1月9日,上午2:14:47
	 */
	public void saveTrunk(JdbcTemplate jdbcSrc, JdbcTemplate jdbcTarget) {
		StringBuilder getTrunkSql = new StringBuilder("select a.id, a.title from trunk a ");
		final List<Map<String, Object>> trunkList = jdbcSrc.queryForList(getTrunkSql.toString());
		
		// 删除原有数据
		StringBuilder delTrunkSql = new StringBuilder("delete from medical_call_relay ");
		jdbcTarget.update(delTrunkSql.toString());
		
		StringBuilder saveTrunkSql = new StringBuilder("insert into medical_call_relay(id, relay_id, name) values(?, ?, ?)");
		jdbcTarget.batchUpdate(saveTrunkSql.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, UuidUtil.getUuid());
				arg0.setString(2, trunkList.get(arg1).get("id").toString());
				arg0.setString(3, trunkList.get(arg1).get("title").toString());
			}
			@Override
			public int getBatchSize() {
				return trunkList.size();
			}
		});
	}
	
	/**
	 * @desc 同步外线号码到业务系统
	 * @author kangyj
	 * @datetime 2015年1月9日,上午1:47:59
	 */
	public void saveOutSide(JdbcTemplate jdbcSrc, JdbcTemplate jdbcTarget) {
		// 获取路由表数据
		StringBuilder getRouteSql = new StringBuilder("select a.id, a.did from inbound_route a ");
		final List<Map<String, Object>> routeList = jdbcSrc.queryForList(getRouteSql.toString());
		
		final List<Map<String, Object>> updateOutSideList = new ArrayList<Map<String,Object>>();
		final List<Map<String, Object>> saveOutSideList = new ArrayList<Map<String,Object>>();
		for(Map<String, Object> route : routeList) {
			StringBuilder sql2 = new StringBuilder("select * from medical_enterprise_outside a where a.outside_id=? ");
			List<Map<String, Object>> OutSideList = jdbcTarget.queryForList(sql2.toString(), new Object[]{route.get("id")});
			
			if(OutSideList.size() > 0 && !OutSideList.get(0).get("outside_num").equals(route.get("did"))) {
				updateOutSideList.add(route);
			} else if(OutSideList.size() == 0) {
				saveOutSideList.add(route);
			}
		}
		
		StringBuilder sql3 = new StringBuilder("update medical_enterprise_outside a set a.outside_num=? where a.outside_id=? ");
		jdbcTarget.batchUpdate(sql3.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, updateOutSideList.get(arg1).get("did").toString());
				arg0.setString(2, updateOutSideList.get(arg1).get("id").toString());
			}
			
			@Override
			public int getBatchSize() {
				return updateOutSideList.size();
			}
		});
		
		StringBuilder sql4 = new StringBuilder("insert into medical_enterprise_outside(id, outside_id, outside_num, eid) values(?,?,?,?)");
		jdbcTarget.batchUpdate(sql4.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, UuidUtil.getUuid());
				arg0.setString(2, saveOutSideList.get(arg1).get("id").toString());
				arg0.setString(3, saveOutSideList.get(arg1).get("did").toString());
				arg0.setString(4, "0");
			}
			
			@Override
			public int getBatchSize() {
				return saveOutSideList.size();
			}
		});
	}
	
	/**
	 * @desc 查询目标数据库外线号码在资源数据库中是否存在，不存在则删除
	 * @param jdbcSrc
	 * @param jdbcTarget
	 * @author kangyj
	 * @datetime 2015年2月4日,下午6:43:40
	 */
	public void updateOutSideNum(JdbcTemplate jdbcSrc, JdbcTemplate jdbcTarget) {
		
		StringBuilder sql1 = new StringBuilder("select * from medical_enterprise_outside");
		List<Map<String, Object>> outsideList = jdbcTarget.queryForList(sql1.toString());
		
		final List<Map<String, Object>> delOutsideList = new ArrayList<Map<String,Object>>();
		for(Map<String, Object> outside : outsideList) {
			StringBuilder sql2 = new StringBuilder("select a.id from inbound_route a where a.id=? ");
			List<Map<String, Object>> inboundList = jdbcSrc.queryForList(sql2.toString(), new Object[]{outside.get("outside_id")});
			if(inboundList.size() == 0) {
				delOutsideList.add(outside);
			}
		}
		
		StringBuilder sql3 = new StringBuilder("delete from medical_enterprise_outside where id=? ");
		jdbcTarget.batchUpdate(sql3.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, delOutsideList.get(arg1).get("id").toString());
			}
			
			@Override
			public int getBatchSize() {
				return delOutsideList.size();
			}
		});
		
	}
	
	/**
	 * @desc 同步队列到业务系统
	 * @author kangyj
	 * @datetime 2015年1月9日,上午2:12:36
	 */
	public void saveQueue(JdbcTemplate jdbcSrc, JdbcTemplate jdbcTarget) {
		
		// 获取队列表数据
		StringBuilder getQueueSql = new StringBuilder("select a.id, a.queue from queue a ");
		final List<Map<String, Object>> queueSrcList = jdbcSrc.queryForList(getQueueSql.toString());
		
		final List<Map<String, Object>> updateQueueList = new ArrayList<Map<String,Object>>();
		final List<Map<String, Object>> saveQueueList = new ArrayList<Map<String,Object>>();
		for(Map<String, Object> queue : queueSrcList) {
			StringBuilder sql2 = new StringBuilder("select * from medical_enterprise_queue a where a.queue_id=? ");
			List<Map<String, Object>> queueTarList = jdbcTarget.queryForList(sql2.toString(), new Object[]{queue.get("id")});
			
			if(queueTarList.size() > 0 && !queueTarList.get(0).get("queue_no").equals(queue.get("queue"))) {
				updateQueueList.add(queue);
			} else if(queueTarList.size() == 0) {
				saveQueueList.add(queue);
			}
		}
		
		StringBuilder sql3 = new StringBuilder("update medical_enterprise_queue a set a.queue_no=? where a.queue_id=? ");
		jdbcTarget.batchUpdate(sql3.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, updateQueueList.get(arg1).get("queue").toString());
				arg0.setString(2, updateQueueList.get(arg1).get("id").toString());
			}
			
			@Override
			public int getBatchSize() {
				return updateQueueList.size();
			}
		});
		
		StringBuilder sql4 = new StringBuilder("insert into medical_enterprise_queue(id, queue_id, queue_no, eid) values(?,?,?,?)");
		jdbcTarget.batchUpdate(sql4.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, UuidUtil.getUuid());
				arg0.setString(2, saveQueueList.get(arg1).get("id").toString());
				arg0.setString(3, saveQueueList.get(arg1).get("queue").toString());
				arg0.setString(4, "0");
			}
			
			@Override
			public int getBatchSize() {
				return saveQueueList.size();
			}
		});
	}
	
	/**
	 * @desc 查询目标数据库队列号在资源数据库中是否存在，不存在则删除
	 * @param jdbcSrc
	 * @param jdbcTarget
	 * @author kangyj
	 * @datetime 2015年2月4日,下午11:47:00
	 */
	public void updateQueue(JdbcTemplate jdbcSrc, JdbcTemplate jdbcTarget) {

		StringBuilder sql1 = new StringBuilder("select * from medical_enterprise_queue");
		List<Map<String, Object>> queueTarList = jdbcTarget.queryForList(sql1.toString());
		
		final List<Map<String, Object>> delQueueList = new ArrayList<Map<String,Object>>();
		for(Map<String, Object> queue : queueTarList) {
			StringBuilder sql2 = new StringBuilder("select a.id from queue a where a.id=? ");
			List<Map<String, Object>> queueSrcList = jdbcSrc.queryForList(sql2.toString(), new Object[]{queue.get("queue_id")});
			if(queueSrcList.size() == 0) {
				delQueueList.add(queue);
			}
		}
		
		StringBuilder sql3 = new StringBuilder("delete from medical_enterprise_queue where id=? ");
		jdbcTarget.batchUpdate(sql3.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, delQueueList.get(arg1).get("id").toString());
			}
			
			@Override
			public int getBatchSize() {
				return delQueueList.size();
			}
		});
	}
	
	/**
	 * @desc 同步分机数据到业务数据库
	 * @param jdbcSrc
	 * @param jdbcTarget
	 * @author kangyj
	 * @datetime 2015年1月28日,上午11:42:03
	 */
	public void saveExtension(JdbcTemplate jdbcSrc, JdbcTemplate jdbcTarget) {
		StringBuilder sql1 = new StringBuilder("select a.id, a.device from device a ");
		List<Map<String, Object>> deviceList = jdbcSrc.queryForList(sql1.toString());
		
		final List<Map<String, Object>> updateDeviceList = new ArrayList<Map<String,Object>>();
		final List<Map<String, Object>> saveExtensionList = new ArrayList<Map<String,Object>>();
		for(Map<String, Object> device : deviceList) {
			StringBuilder sql2 = new StringBuilder("select * from medical_enterprise_extension a where a.extension_id=? ");
			List<Map<String, Object>> extensionList = jdbcTarget.queryForList(sql2.toString(), new Object[]{device.get("id")});
			if(extensionList.size() > 0 && !extensionList.get(0).get("extension").equals(device.get("device"))) {
				updateDeviceList.add(device);
			} else if(extensionList.size() == 0) {
				saveExtensionList.add(device);
			}
		}
		
		StringBuilder sql3 = new StringBuilder("update medical_enterprise_extension a set a.extension=? where a.extension_id=? ");
		jdbcTarget.batchUpdate(sql3.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, updateDeviceList.get(arg1).get("device").toString());
				arg0.setString(2, updateDeviceList.get(arg1).get("id").toString());
			}
			
			@Override
			public int getBatchSize() {
				return updateDeviceList.size();
			}
		});
		
		StringBuilder sql4 = new StringBuilder("insert into medical_enterprise_extension(id, extension_id, extension, eid) values(?,?,?,?)");
		jdbcTarget.batchUpdate(sql4.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, UuidUtil.getUuid());
				arg0.setString(2, saveExtensionList.get(arg1).get("id").toString());
				arg0.setString(3, saveExtensionList.get(arg1).get("device").toString());
				arg0.setString(4, "0");
			}
			
			@Override
			public int getBatchSize() {
				return saveExtensionList.size();
			}
		});
	}
	
	/**
	 * @desc 查询目标数据库分机在资源数据库中是否存在，不存在则删除
	 * @param jdbcSrc
	 * @param jdbcTarget
	 * @author kangyj
	 * @datetime 2015年1月28日,下午1:44:25
	 */
	public void updateExtension(JdbcTemplate jdbcSrc, JdbcTemplate jdbcTarget) {
		
		StringBuilder sql1 = new StringBuilder("select * from medical_enterprise_extension");
		List<Map<String, Object>> extensionList = jdbcTarget.queryForList(sql1.toString());
		
		final List<Map<String, Object>> delExtensionList = new ArrayList<Map<String,Object>>();
		for(Map<String, Object> extension : extensionList) {
			StringBuilder sql2 = new StringBuilder("select a.id from device a where a.id=? ");
			List<Map<String, Object>> deviceList = jdbcSrc.queryForList(sql2.toString(), new Object[]{extension.get("extension_id")});
			if(deviceList.size() == 0) {
				delExtensionList.add(extension);
			}
		}
		
		StringBuilder sql3 = new StringBuilder("delete from medical_enterprise_extension where id=? ");
		jdbcTarget.batchUpdate(sql3.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, delExtensionList.get(arg1).get("id").toString());
			}
			
			@Override
			public int getBatchSize() {
				return delExtensionList.size();
			}
		});
	}

}
